SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[Kernel].[ApplicantWith]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [Kernel].[ApplicantWith]
END

GO

/*****************************************************************************/
/* Stored Procedure ::                                                       */
/*****************************************************************************/
/* Copyright (C) 2007, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Created:  10/06/2007                                                      */
/*****************************************************************************/
CREATE PROCEDURE [Kernel].[ApplicantWith]( @ApplicantID BIGINT )
AS  
BEGIN

SELECT 
		Applicants.OID, 
		Applicants.Version as 'ApplicantVersion',
		Applicants.KinshipID,
		TypesOfKinship.Description as 'Kinship',
		Applicants.AddressID, 
		People.Version as 'PeopleVersion', 
		People.FirstName, 
		People.LastName, 
		People.MiddleInitial, 
		People.DOB, 
		People.Sex, 
		People.Phone1, 
		People.Phone2, 
		People.eMailAddress 
	From Applicants
	INNER JOIN People On Applicants.OID = People.OID
	INNER JOIN TypesOfKinship ON TypesOfKinship.OID = Applicants.KinshipID
	Where Applicants.OID = @ApplicantID

END 
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [Kernel].[ApplicantWith] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership    